A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
1. Field of the Invention
The present invention relates generally to information processing environments and, more particularly, to improved methods for logging of transactions which are posted in a data processing system, such as a database management system (DBMS).
2. Description of the Background Art
Computers are very powerful tools for storing and providing access to vast amounts of information. Computer databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical database is an organized collection of related information stored as xe2x80x9crecordsxe2x80x9d having xe2x80x9cfieldsxe2x80x9d of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.
Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about underlying hardware-level details. Typically, all requests from users for access to the data are processed by the DBMS. For example, information may be added or removed from data files, information retrieved from or updated in such files, and so forth, all without user knowledge of the underlying system implementation. In this manner, the DBMS provides users with a conceptual view of the database that is removed from the hardware level.
DBMS systems have long since moved from a centralized mainframe environment to a de-centralized or distributed environment. Today, one generally finds database systems implemented as one or more PC xe2x80x9cclientxe2x80x9d systems, for instance, connected via a network to one or more server-based database systems (SQL database server). Commercial examples of these xe2x80x9cclient/serverxe2x80x9d systems include Powersoft(copyright) clients connected to one or more Sybase(copyright) Adaptive Server(copyright) Enterprise database servers. Both Powersoft(copyright) and Sybase(copyright) Adaptive Server(copyright) Enterprise (formerly Sybase(copyright) SQL Server(copyright)) are available from Sybase, Inc. of Dublin, Calif. The general construction and operation of database management systems, including xe2x80x9cclient/serverxe2x80x9d relational database systems, is well known in the art. See e.g., Date, C., xe2x80x9cAn Introduction to Database Systems, Volume I and II,xe2x80x9d Addison Wesley, 1990; the disclosure of which is hereby incorporated by reference.
Traditionally, database management systems (e.g., the above-described client/server database systems) have been employed for on-line transaction processing (OLTP) involving the posting of data from xe2x80x9ctransactionsxe2x80x9d to a database table. As part of this process, OLTP systems typically employ a logging system to log changes which occur to the system. In a commercial embodiment such as Sybase Adaptive Server Enterprise, this is done by copying log records to a transaction log. Every transactional operation, including inserts, updates, and deletes, causes a log record to be written to the transaction log or simply xe2x80x9clog.xe2x80x9d Each particular log record characterizes the change which has occurred to the database during processing of a transaction. This information can be used, for instance, in error recovery, to restore the database to a preexisting, consistent state.
Consider a scenario where a transaction performs updates to a table but then the transaction xe2x80x9crolls backxe2x80x9dxe2x80x94that is, aborts. In such a case, the system will undo the updates by reading backwards from the log and reversing the changes which were made (as a result of the updates). The recovery system of databases, therefore, employs the logging system and log records when performing the work of rolling back a transaction. In a similar fashion, the log can be used in the face of a failure, such as when a machine xe2x80x9ccrashes.xe2x80x9d As the log is read during recovery, some transactions are re-done on the one hand, while incomplete transactions are undone on the other. In addition to rolling back transactions and supporting error recovery, the log also provides an archive for the database, which documents the specific actions which have led to the current state of the database. All told, the log plays a critical part in the design and implementation of present-day relational database systems.
The logging system itself permits reading from and writing to the log. Write access is typically performed by xe2x80x9caccess methodsxe2x80x9d within a relational database system (i.e., a database system which presents data as tables or xe2x80x9crelationsxe2x80x9d). In particular, these methods generate log records which describe actions occurring which affect the database. Read access, on the other hand, is generally provided by a recovery system within the database. In general, therefore, a database system includes systems for writing log records into the log and, if needed, reading back those records.
A general description of the design and implementation of a logging system in a relational database is provided by Gray, J. and Reuter, A., xe2x80x9cTransaction Processing: Concepts and Techniques,xe2x80x9d Morgan Kaufmann Publishers, 1993, the disclosure of which is hereby incorporated by reference. For an overview of relational database systems, see the abovementioned xe2x80x9cAn Introduction to Database Systems,xe2x80x9d the disclosure of which has been previously incorporated by reference.
Each day more and more businesses are run from mission-critical systems which store information on server-based database systems, such as Sybase Adaptive Server Enterprise. As a result, increasingly higher demands are being placed on server-based database management systems to xe2x80x9cscalexe2x80x9d with increased hardware resourcesxe2x80x94that is, as more sophisticated hardware (e.g., multi-processor units) becomes available, these systems should provide greater throughput.
The logging system of a database system presents a bottleneck to system scalability, however. This is because every insert, update, and delete operation must make a log entry to protect the database from corruption if a system failure or transaction rollback occurs. Most relational databases process a log entry for each update, insert, or delete statement, and each log entry is processed one at a time. When a log entry is written, the logging system must navigate through a synchronization point referred to as the xe2x80x9clog semaphorexe2x80x9d which controls concurrent access to the log by multiple database transactions. Because every transaction involves the logging system, its efficiency is paramount to transaction throughput. As scalability increases in a database system and transaction volume increases, the contention for logging resources dramatically increases, resulting in reduced system throughput.
One way for reducing contention for logging resources in a transaction processing system is to provide a private log cache which provides an area of memory where log records relating to a user""s task are built and stored before being posted to the log. Each private log cache may hold multiple log records for a transaction. The private log cache is only written to the log when a transaction commits or when memory fills with log records, thereby reducing steady state contention on the logging system. For further description of a database server system having a private log cache see commonly-owned U.S. Pat. No. 6,321,234, xe2x80x9cDatabase Server System with Improved Methods for Logging Transactions.xe2x80x9d The disclosure of the foregoing is hereby incorporated by reference for all purposes.
Although use of a private logging cache reduces steady state contention on logging resources, several problems remain in logging systems of current database management systems that impact performance (e.g., throughput) of such systems. A number of the logging operations of current database management systems involve sequential operations against the logging system of the database system. As such, a large bottleneck may be created; particularly in the context of large OLTP systems as multiple transactions seek to access shared logging resources at the same time.
Other factors are adding to the demands that are being placed on logging systems of databases. Applications have become more complex with multi-tier systems consolidating various parts of an enterprise system and generating increased load and traffic on the underlying database systems. In addition, users of these systems are increasing upgrading the support hardware to machines with multiple processors capable of performing millions of transactions. This increased number of processors presents a new set of challenges for database systems. An increased number of processors (CPUs) increases contention on some of the key system resources, such as logging resources.
What is needed is a solution which preserves database throughput by reducing the contention which occurs for logging resources, even when a database system is handling hundreds or thousands of transactions per minute. The present invention fulfills this and other needs.
The following definitions are offered for purposes of illustration, not limitation, in order to assist with understanding the discussion that follows.
OLTP or Transaction Processing: A transaction processing or OLTP system is a type of computer processing system in which the system responds immediately to user requests. Each user request is considered to be a transaction. Automatic teller machines for banks are one example of a transaction processing system. Transaction processing involves interaction with a user, whereas batch processing can take place without a user being present.
Spin lock: A spin lock is a multi-processor synchronization mechanism. As is known in the art, a spin lock is a synchronization mechanism, such as a mutex (mutually-exclusive access), except that with a spin lock the process or thread does not xe2x80x9csleepxe2x80x9d on the lock. Instead, it xe2x80x9cspinsxe2x80x9dxe2x80x94continues to execute waiting for the lock to be lifted. By spinning instead of sleeping, the process avoids the overhead and cost of a context switch. Most often, spin locks are employed for access to critical resources.
SQL: SQL stands for Structured Query Language, which has become the standard for relational database access, see e.g., xe2x80x9cInformation Technologyxe2x80x94Database languagesxe2x80x94SQL,xe2x80x9d published by the American National Standards Institute as American National Standard ANSI/ISO/IEC 9075: 1992, the disclosure of which is hereby incorporated by reference. For additional information regarding SQL in database systems, see e.g., Date, C., xe2x80x9cAn Introduction to Database Systems, Volume I and II,xe2x80x9d Addison Wesley, 1990, the disclosure of which is hereby incorporated by reference.
Task: A task refers to the combination of a program or application being executed and related booking information used by the system. Whenever a particular program or application is executed, a task is created which serves as an envelope for the program. The task identifies the program with a task number and attaches other bookkeeping information to it. Many systems, including database systems, are capable of performing many tasks at the same time and are called multi-tasking systems. The terms task and process are sometimes used interchangeably, although some systems make a distinction between the two.
Thread: A thread refers to a part of a program that can execute independently of other parts. Operating systems that support multi-threading enable programmers to design programs whose threaded parts can execute concurrently. In some systems, there is a one-to-one relationship between the task and the program, but a multi-threaded system allows a program to be divided into multiple tasks. Multi-threaded programs may have several threads running through different code paths simultaneously.
The asynchronous logging system of the present invention provides improved methods for storing log records in a manner that reduces contention for logging resources of a database system. Log records are created describing changes to the database for a given transaction. When a command which commits changes to the database for a transaction is received, a request to store log records for the transaction is automatically placed in a request queue. Requests are removed from the request queue in sequence and log records from the transaction associated with the request are transferred to a shared cache. The shared cache contains log pages for storing log records from multiple transactions before such log records are written to the transaction log. After log records for the transaction are transferred to log pages in the shared cache, the log pages are written from the shared cache to the transaction log. After all log pages in the shared cache for a given transaction have been written to the transaction log, changes to the database made by the transaction can be committed to the database.
The asynchronous logging system of the present invention includes three modules or threads. A log allocation module or thread monitors unwritten log pages available in the shared cache and allocates additional log pages to the shared cache when it determines that additional log pages are required. A flusher module or thread receives requests for storing log records for transactions from a plurality of transactions. The flusher module handles each of these requests on behalf of a given transaction by transferring log records for the transaction to the shared cache. A log writer module or thread monitors log pages in the shared cache which contain log records. When the log writer module finds log pages containing log records in the shared cache, it writes these log pages to the transaction log. The log writer notifies the applicable client or transaction after all log records for a transaction have been written to the transaction log.